from typing import List

from sqlalchemy.orm import Mapped, mapped_column, relationship, declarative_base, sessionmaker
from sqlalchemy import String, insert, update, select, delete, ForeignKey, Table, Column, create_engine

engine = create_engine("mysql+pymysql://root:123456@localhost:3308/demo?charset=utf8mb4", echo=True)
Base = declarative_base()

Session = sessionmaker(engine)

assocation_table = Table(
    "user_role",
    Base.metadata,
    Column("user_id", ForeignKey("user.id"), primary_key=True),
    Column("role_id", ForeignKey("role.id"), primary_key=True)
)


class Role(Base):
    __tablename__ = "role"

    id: Mapped[int] = mapped_column(primary_key=True, nullable=False)
    name: Mapped[str] = mapped_column(String(200))

    users: Mapped[List["User"]] = relationship(secondary=assocation_table)

    # def __repr__(self):
    #     print(f"id:{self.id}   name:{self.name}")


class User(Base):
    __tablename__ = "user"

    id: Mapped[int] = mapped_column(primary_key=True, nullable=False)
    name: Mapped[str] = mapped_column(String(200))
    password: Mapped[str] = mapped_column(String(100))

    roles: Mapped[List["Role"]] = relationship(secondary=assocation_table)
    cars: Mapped[List["Car"]] = relationship()
    # def __repr__(self):
    #     print(f"id:{self.id}   name:{self.name}")

class Nation(Base):
    __tablename__ = "nation"

    id: Mapped[int] = mapped_column(primary_key=True, nullable=False)
    name: Mapped[str] = mapped_column(String(200))
    location: Mapped[str] = mapped_column(String(200))

class Producer(Base):
    __tablename__ = "producer"

    id: Mapped[int] = mapped_column(primary_key=True, nullable=False)
    name: Mapped[str] = mapped_column(String(200))
    nation_id: Mapped[int] = mapped_column(ForeignKey("nation.id"))

    nation: Mapped[Nation] = relationship()


class Car(Base):
    __tablename__ = "industry"

    id: Mapped[int] = mapped_column(primary_key=True, nullable=False)
    name: Mapped[str] = mapped_column(String(200))
    producer_id: Mapped[int] = mapped_column(ForeignKey("producer.id"))
    user_id: Mapped[int] = mapped_column(ForeignKey("user.id"))

    producer: Mapped[Producer] = relationship()
    user: Mapped[User] = relationship()

Base.metadata.create_all(engine)

with Session() as session:
    item = session.query(User).filter(User.id == 10).first()
    print(f"###  user:{item.name}")
    for role in item.roles:
        print(f"###  role:{role.name}")
    for car in item.cars:
        print(f"###  industry:{car.name}  producer:{car.producer.name}  nation:{car.producer.nation.name}")
    item1 = session.query(Role).filter(Role.id == 1).first()
    print("========================================")
    print(f"###  role:{item1.name}")
    for user in item1.users:
        print(f"###  role user:{user.name}")
